/*
 * MIT License
 *
 * Copyright (c) 2023 北京凯特伟业科技有限公司
 *
 * Permission is hereby granted, free of charge, to any person obtaining a copy
 * of this software and associated documentation files (the "Software"), to deal
 * in the Software without restriction, including without limitation the rights
 * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
 * copies of the Software, and to permit persons to whom the Software is
 * furnished to do so, subject to the following conditions:
 *
 * The above copyright notice and this permission notice shall be included in all
 * copies or substantial portions of the Software.
 *
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
 * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
 * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
 * SOFTWARE.
 */
package com.je.common.base.table.service.impl;

import com.google.common.base.Joiner;
import com.google.common.base.Strings;
import com.je.common.base.DynaBean;
import com.je.common.base.constants.table.ColumnType;
import com.je.common.base.entity.extjs.DbModel;
import com.je.common.base.exception.PlatformException;
import com.je.common.base.exception.PlatformExceptionEnum;
import com.je.common.base.service.MetaService;
import com.je.common.base.service.rpc.BeanService;
import com.je.common.base.table.service.BuildingSqlService;
import com.je.common.base.table.service.TableIndexRpcService;
import com.je.common.base.util.ArrayUtils;
import com.je.common.base.util.DateUtils;
import com.je.common.base.util.StringUtil;
import net.sf.json.JSONObject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

/**
 * @program: jecloud-common
 * @author: LIULJ
 * @create: 2021-03-01 16:53
 * @description:
 */
@Service("buildingMySqlServiceImpl")
public class BuildingMySqlServiceImpl implements BuildingSqlService {

    public Logger logger = LoggerFactory.getLogger(getClass());

    @Autowired
    private BeanService beanService;
    @Autowired
    private TableIndexRpcService tableIndexRpcService;

    @Autowired
    private MetaService metaService;


    /**
     * 辅助函数,生成添加列的SQL
     *
     * @param column
     * @return
     */
    private String getDDL4AddColumns(DynaBean column, Boolean jeCore) {
        StringBuilder builder = new StringBuilder();
        String type = column.getStr("TABLECOLUMN_TYPE");
        String code = column.getStr("TABLECOLUMN_CODE");
        String isNull = column.getStr("TABLECOLUMN_ISNULL");
        String length = column.getStr("TABLECOLUMN_LENGTH");
        if (ColumnType.ID.equals(type)) {
            //id
            builder.append(code + " VARCHAR(50) not null,\n");
        } else if (ColumnType.CUSTOMID.equals(type)) {
            //id
            if(Strings.isNullOrEmpty(length)){
                builder.append(code + " BIGINT(20) not null,\n");
            }else {
                builder.append(code + " " + length + " not null,\n");
            }
        } else if (ColumnType.CLOB.equals(type)) {
            //大数据
            builder.append(code + " text");
            if (!"1".equals(isNull)) {
                builder.append(" not null");
            }
            builder.append(",\n");
        } else if (ColumnType.BIGCLOB.equals(type)) {
            //大数据
            builder.append(code + " longtext");
            if (!"1".equals(isNull)) {
                builder.append(" not null");
            }
            builder.append(",\n");
        } else if (ColumnType.DATE.equals(type)) {
            //时间
            builder.append(code + " VARCHAR(12)");
            if (!"1".equals(isNull)) {
                builder.append(" not null");
            }
            builder.append(",\n");
        } else if (ColumnType.DATETIME.equals(type)) {
            //时间
            builder.append(code + " VARCHAR(20)");
            if (!"1".equals(isNull)) {
                builder.append(" not null");
            }
            builder.append(",\n");
        } else if (ColumnType.FLOAT.equals(type)) {
            String numVal = "";
            if (!Strings.isNullOrEmpty(length)) {
                if (length.indexOf(",") != -1) {
                    numVal = length.replace(",", "@");
                } else {
                    numVal = "20@" + length;
                }
            } else {
                numVal = "20@2";
            }
//			Integer lengthh=2;
//			if(StringUtil.isNotEmpty(length)){
//				lengthh=Integer.parseInt(length);
//			}
            //小数
            builder.append(code + " DECIMAL(" + numVal + ")");
            //小数
            if (!"1".equals(isNull)) {
                builder.append(" not null");
            }
            builder.append(",\n");
        } else if (ColumnType.FLOAT2.equals(type)) {
            Integer lengthh = 2;
            //小数
            builder.append(code + " DECIMAL(20@" + lengthh + ")");
            //小数
            if (!"1".equals(isNull)) {
                builder.append(" not null");
            }
            builder.append(",\n");
        } else if (ColumnType.CUSTOM.equals(type)) {
            //自定义,当选择自定义的时候长度 就写成类似(VARCHAE2(154))
            if (Strings.isNullOrEmpty(length)) {
                builder.append(code + String.format("VARCHAR(154)"));
            } else {
                length = length.replaceAll(",", "@");
                builder.append(code + " " + length);
            }
            if (!"1".equals(isNull)) {
                builder.append(" not null");
            }
            builder.append(",\n");
        } else if (ColumnType.NUMBER.equals(type)) {
            //整数
            if (Strings.isNullOrEmpty(length)) {
                length = "11";
            }
            builder.append(code + " int(" + length + ")");
            if (!"1".equals(isNull)) {
                builder.append(" not null");
            }
            builder.append(",\n");
        } else if (ColumnType.VARCHAR767.equals(type)) {
            //大字符串
            builder.append(code + " VARCHAR(767)");
            if (!"1".equals(isNull)) {
                builder.append(" not null");
            }
            builder.append(",\n");
        } else if (ColumnType.VARCHAR1000.equals(type)) {
            //大字符串
            builder.append(code + " VARCHAR(1000)");
            if (!"1".equals(isNull)) {
                builder.append(" not null");
            }
            builder.append(",\n");
        } else if (ColumnType.VARCHAR2000.equals(type)) {
            //大字符串
            builder.append(code + " VARCHAR(2000)");
            if (!"1".equals(isNull)) {
                builder.append(" not null");
            }
            builder.append(",\n");
        } else if (ColumnType.VARCHAR4000.equals(type)) {
            //大字符串
            if (jeCore || ("SYS".equals(column.getStr("TABLECOLUMN_CLASSIFY")))) {
                builder.append(code + " text");
            } else {
                builder.append(code + " VARCHAR(4000)");
            }
            if (!"1".equals(isNull)) {
                builder.append(" not null");
            }
            builder.append(",\n");
        } else if (ColumnType.VARCHAR255.equals(type)) {
            //一般字符串
            builder.append(code + " VARCHAR(255)");
            if (!"1".equals(isNull)) {
                builder.append(" not null");
            }
            builder.append(",\n");
        } else if (ColumnType.VARCHAR100.equals(type)) {
            //一般字符串
            builder.append(code + " VARCHAR(100)");
            if (!"1".equals(isNull)) {
                builder.append(" not null");
            }
            builder.append(",\n");
        } else if (ColumnType.VARCHAR50.equals(type)) {
            //一般字符串
            builder.append(code + " VARCHAR(50)");
            if (!"1".equals(isNull)) {
                builder.append(" not null");
            }
            builder.append(",\n");
        } else if (ColumnType.VARCHAR30.equals(type)) {
            //一般字符串
            builder.append(code + " VARCHAR(30)");
            if (!"1".equals(isNull)) {
                builder.append(" not null");
            }
            builder.append(",\n");
        } else if (ColumnType.VARCHAR.equals(type)) {
            if ("MAX".equalsIgnoreCase(length)) {
                //自定义长度字符串
                builder.append(code + " text");
            } else if ("900".equals(length) && ("SY_TREEORDERINDEX".equals(code) || "TREEORDERINDEX".equals(code))) {
                builder.append(code + " VARCHAR(383)");
            } else {
                //自定义长度字符串
                builder.append(code + " VARCHAR(" + length + ")");
            }
            if (!"1".equals(isNull)) {
                builder.append(" not null");
            }
            builder.append(",\n");
        } else if (ColumnType.YESORNO.equals(type)) {
            //YESORNO
            builder.append(code + " VARCHAR(4)");
            if (!"1".equals(isNull)) {
                builder.append(" not null");
            }
            builder.append(",\n");
        } else if (ColumnType.FOREIGNKEY.equals(type)) {
            builder.append(code + " VARCHAR(50)");
            if (!"1".equals(isNull)) {
                builder.append(" not null");
            }
            builder.append(",\n");
        } else if (ColumnType.CUSTOMFOREIGNKEY.equals(type)) {
            if(Strings.isNullOrEmpty(length)) {
                builder.append(code + " VARCHAR(50)");
            }else {
                builder.append(code + " " + length);
            }
            if (!"1".equals(isNull)) {
                builder.append(" not null");
            }
            builder.append(",\n");
        } else {
            throw new PlatformException("数据表格SQL构建异常", PlatformExceptionEnum.JE_CORE_TABLE_BUILDING_ERROR, new Object[]{column, jeCore});
        }
        return builder.toString();
    }

    /**
     * 辅助函数,生成添加列的SQL
     *
     * @param column
     * @return
     */
    private String getDDL4AddColumnsForAuto(DynaBean column, Boolean jeCore, DynaBean resourceTable) {
        StringBuilder builder = new StringBuilder();
        String type = column.getStr("TABLECOLUMN_TYPE");
        String code = column.getStr("TABLECOLUMN_CODE");
        String isNull = column.getStr("TABLECOLUMN_ISNULL");
        String length = column.getStr("TABLECOLUMN_LENGTH");
        if (ColumnType.ID.equals(type)) {
            //id
            if ("AUTO".equals(resourceTable.getStr("RESOURCETABLE_KEY_GENERATOR_TYPE"))
                    ||"AUTO_INCREMENTER".equals(resourceTable.getStr("RESOURCETABLE_KEY_GENERATOR_TYPE"))){
                builder.append(code + " BIGINT(20) not null AUTO_INCREMENT,\n");
            }else {
                builder.append(code + " VARCHAR(50) not null,\n");
            }
        } else if (ColumnType.CUSTOMID.equals(type)) {
            if ("AUTO".equals(resourceTable.getStr("RESOURCETABLE_KEY_GENERATOR_TYPE"))
                    ||"AUTO_INCREMENTER".equals(resourceTable.getStr("RESOURCETABLE_KEY_GENERATOR_TYPE"))){
                if(Strings.isNullOrEmpty(length)){
                    builder.append(code + " BIGINT(20) not null AUTO_INCREMENT,\n");
                }else {
                    builder.append(code + " " + length + " not null AUTO_INCREMENT,\n");
                }
            }else {
                //id
                if(Strings.isNullOrEmpty(length)){
                    builder.append(code + " BIGINT(20) not null,\n");
                }else {
                    builder.append(code + " " + length + " not null,\n");
                }
            }
        } else if (ColumnType.CLOB.equals(type)) {
            //大数据
            builder.append(code + " text");
            if (!"1".equals(isNull)) {
                builder.append(" not null");
            }
            builder.append(",\n");
        } else if (ColumnType.BIGCLOB.equals(type)) {
            //大数据
            builder.append(code + " longtext");
            if (!"1".equals(isNull)) {
                builder.append(" not null");
            }
            builder.append(",\n");
        } else if (ColumnType.DATE.equals(type)) {
            //时间
            builder.append(code + " VARCHAR(12)");
            if (!"1".equals(isNull)) {
                builder.append(" not null");
            }
            builder.append(",\n");
        } else if (ColumnType.DATETIME.equals(type)) {
            //时间
            builder.append(code + " VARCHAR(20)");
            if (!"1".equals(isNull)) {
                builder.append(" not null");
            }
            builder.append(",\n");
        } else if (ColumnType.FLOAT.equals(type)) {
            String numVal = "";
            if (!Strings.isNullOrEmpty(length)) {
                if (length.indexOf(",") != -1) {
                    numVal = length.replace(",", "@");
                } else {
                    numVal = "20@" + length;
                }
            } else {
                numVal = "20@2";
            }
//			Integer lengthh=2;
//			if(StringUtil.isNotEmpty(length)){
//				lengthh=Integer.parseInt(length);
//			}
            //小数
            builder.append(code + " DECIMAL(" + numVal + ")");
            //小数
            if (!"1".equals(isNull)) {
                builder.append(" not null");
            }
            builder.append(",\n");
        } else if (ColumnType.FLOAT2.equals(type)) {
            Integer lengthh = 2;
            //小数
            builder.append(code + " DECIMAL(20@" + lengthh + ")");
            //小数
            if (!"1".equals(isNull)) {
                builder.append(" not null");
            }
            builder.append(",\n");
        } else if (ColumnType.CUSTOM.equals(type)) {
            //自定义,当选择自定义的时候长度 就写成类似(VARCHAE2(154))
            if (Strings.isNullOrEmpty(length)) {
                builder.append(code + String.format("VARCHAR(154)"));
            } else {
                length = length.replaceAll(",", "@");
                builder.append(code + " " + length);
            }
            if (!"1".equals(isNull)) {
                builder.append(" not null");
            }
            builder.append(",\n");
        } else if (ColumnType.NUMBER.equals(type)) {
            //整数
            if (Strings.isNullOrEmpty(length)) {
                length = "11";
            }
            builder.append(code + " int(" + length + ")");
            if (!"1".equals(isNull)) {
                builder.append(" not null");
            }
            builder.append(",\n");
        } else if (ColumnType.VARCHAR767.equals(type)) {
            //大字符串
            builder.append(code + " VARCHAR(767)");
            if (!"1".equals(isNull)) {
                builder.append(" not null");
            }
            builder.append(",\n");
        } else if (ColumnType.VARCHAR1000.equals(type)) {
            //大字符串
            builder.append(code + " VARCHAR(1000)");
            if (!"1".equals(isNull)) {
                builder.append(" not null");
            }
            builder.append(",\n");
        } else if (ColumnType.VARCHAR2000.equals(type)) {
            //大字符串
            builder.append(code + " VARCHAR(2000)");
            if (!"1".equals(isNull)) {
                builder.append(" not null");
            }
            builder.append(",\n");
        } else if (ColumnType.VARCHAR4000.equals(type)) {
            //大字符串
            if (jeCore || ("SYS".equals(column.getStr("TABLECOLUMN_CLASSIFY")))) {
                builder.append(code + " text");
            } else {
                builder.append(code + " VARCHAR(4000)");
            }
            if (!"1".equals(isNull)) {
                builder.append(" not null");
            }
            builder.append(",\n");
        } else if (ColumnType.VARCHAR255.equals(type)) {
            //一般字符串
            builder.append(code + " VARCHAR(255)");
            if (!"1".equals(isNull)) {
                builder.append(" not null");
            }
            builder.append(",\n");
        } else if (ColumnType.VARCHAR100.equals(type)) {
            //一般字符串
            builder.append(code + " VARCHAR(100)");
            if (!"1".equals(isNull)) {
                builder.append(" not null");
            }
            builder.append(",\n");
        } else if (ColumnType.VARCHAR50.equals(type)) {
            //一般字符串
            builder.append(code + " VARCHAR(50)");
            if (!"1".equals(isNull)) {
                builder.append(" not null");
            }
            builder.append(",\n");
        } else if (ColumnType.VARCHAR30.equals(type)) {
            //一般字符串
            builder.append(code + " VARCHAR(30)");
            if (!"1".equals(isNull)) {
                builder.append(" not null");
            }
            builder.append(",\n");
        } else if (ColumnType.VARCHAR.equals(type)) {
            if ("MAX".equalsIgnoreCase(length)) {
                //自定义长度字符串
                builder.append(code + " text");
            } else if ("900".equals(length) && ("SY_TREEORDERINDEX".equals(code) || "TREEORDERINDEX".equals(code))) {
                builder.append(code + " VARCHAR(383)");
            } else {
                //自定义长度字符串
                builder.append(code + " VARCHAR(" + length + ")");
            }
            if (!"1".equals(isNull)) {
                builder.append(" not null");
            }
            builder.append(",\n");
        } else if (ColumnType.YESORNO.equals(type)) {
            //YESORNO
            builder.append(code + " VARCHAR(4)");
            if (!"1".equals(isNull)) {
                builder.append(" not null");
            }
            builder.append(",\n");
        } else if (ColumnType.FOREIGNKEY.equals(type)) {
            builder.append(code + " VARCHAR(50)");
            if (!"1".equals(isNull)) {
                builder.append(" not null");
            }
            builder.append(",\n");
        } else if (ColumnType.CUSTOMFOREIGNKEY.equals(type)) {
            if(Strings.isNullOrEmpty(length)) {
                builder.append(code + " VARCHAR(50)");
            }else {
                builder.append(code + " " + length);
            }
            if (!"1".equals(isNull)) {
                builder.append(" not null");
            }
            builder.append(",\n");
        } else {
            throw new PlatformException("数据表格SQL构建异常", PlatformExceptionEnum.JE_CORE_TABLE_BUILDING_ERROR, new Object[]{column, jeCore});
        }
        return builder.toString();
    }

    /**
     * 返回字段的类型
     *
     * @param column
     * @return
     */
    private String getDDLFieldType(DynaBean column, Boolean jeCore) {
        String type = "";
        String columnType = column.getStr("TABLECOLUMN_TYPE");
        String length = column.getStr("TABLECOLUMN_LENGTH");
        String code = column.getStr("TABLECOLUMN_CODE");
        if (ColumnType.ID.equals(columnType)) {
            //id
            type = "VARCHAR(50)";
        } else if (ColumnType.CUSTOMID.equals(columnType)) {
            //id
            if(Strings.isNullOrEmpty(length)){
                type = "BIGINT(20)";
            }else {
                type = length;
            }
        } else if (ColumnType.CLOB.equals(columnType)) {
            //大数据
            type = "text";
        } else if (ColumnType.BIGCLOB.equals(columnType)) {
            //大数据
            type = "longtext";
        } else if (ColumnType.DATE.equals(columnType)) {
            type = "VARCHAR(12)";
        } else if (ColumnType.DATETIME.equals(columnType)) {
            //时间
            type = "VARCHAR(20)";
        } else if (ColumnType.FLOAT.equals(columnType)) {
            Integer lengthh = 2;
            if (!Strings.isNullOrEmpty(length) && length.indexOf(",") > 0) {
                String[] typeArray = length.split(",");
                type = "DECIMAL(" + typeArray[0] + "," + typeArray[1] + ")";
            } else {
                if (!Strings.isNullOrEmpty(length)) {
                    lengthh = Integer.parseInt(length);
                }
                //小数
                type = "DECIMAL(20," + lengthh + ")";
            }
        } else if (ColumnType.FLOAT2.equals(columnType)) {
            Integer lengthh = 2;
            //小数
            type = "DECIMAL(20," + lengthh + ")";
        } else if (ColumnType.CUSTOM.equals(columnType)) {
            //自定义,当选择自定义的时候长度 就写成类似(VARCHAE2(154))
            if (Strings.isNullOrEmpty(length)) {
                type = " VARCHAR(154)";
            } else {
                type = length;
            }
        } else if (ColumnType.NUMBER.equals(columnType)) {
            //整数
            type = "int";
        } else if (ColumnType.VARCHAR767.equals(columnType)) {
            //大字符串
            type = "VARCHAR(767)";
        } else if (ColumnType.VARCHAR1000.equals(columnType)) {
            //大字符串
            type = "VARCHAR(1000)";
        } else if (ColumnType.VARCHAR2000.equals(columnType)) {
            //大字符串
            type = "VARCHAR(2000)";
        } else if (ColumnType.VARCHAR4000.equals(columnType)) {
            if (jeCore || ("SYS".equals(column.getStr("TABLECOLUMN_CLASSIFY")))) {
                //大字符串
                type = "text";
            } else {
                type = "VARCHAR(4000)";
            }
        } else if (ColumnType.VARCHAR255.equals(columnType)) {
            //一般字符串
            type = "VARCHAR(255)";
        } else if (ColumnType.VARCHAR100.equals(columnType)) {
            //一般字符串
            type = "VARCHAR(100)";
        } else if (ColumnType.VARCHAR50.equals(columnType)) {
            //一般字符串
            type = "VARCHAR(50)";
        } else if (ColumnType.VARCHAR30.equals(columnType)) {
            //一般字符串
            type = "VARCHAR(30)";
        } else if (ColumnType.VARCHAR.equals(columnType)) {
            if ("MAX".equalsIgnoreCase(length)) {
                type = "text";
            } else if ("900".equals(length) && ("SY_TREEORDERINDEX".equals(code) || "TREEORDERINDEX".equals(code))) {
                type = "VARCHAR(383)";
            } else {
                //自定义长度字符串
                type = "VARCHAR(" + column.getStr("TABLECOLUMN_LENGTH") + ")";
            }
        } else if (ColumnType.YESORNO.equals(columnType)) {
            //YESORNO
            type = "VARCHAR(4)";
        } else if (ColumnType.FOREIGNKEY.equals(columnType)) {
            type = "VARCHAR(50)";
        } else if (ColumnType.CUSTOMFOREIGNKEY.equals(columnType)) {
            if(Strings.isNullOrEmpty(length)) {
                type = "VARCHAR(50)";
            }else {
                type = length;
            }
        } else {
            throw new PlatformException("数据表格SQL构建异常", PlatformExceptionEnum.JE_CORE_TABLE_BUILDING_ERROR, new Object[]{column, jeCore});
        }
        return type;
    }

    /**
     * 辅助函数 生成创建键的DDL语句
     *
     * @param key
     * @param resourceTable
     * @return
     */
    @Override
    public void getDDL4AddKey(DynaBean key, DynaBean resourceTable, List<String> lists) {
        StringBuilder builder = new StringBuilder();
        if ("Primary".equals(key.getStr("TABLEKEY_TYPE"))) {
            builder.append("alter table " + resourceTable.getStr("RESOURCETABLE_TABLECODE") + " \n " +
                    "add primary key (" + key.getStr("TABLEKEY_COLUMNCODE") + ")"
            );
            lists.add(builder.toString());
        } else if ("Unique".equals(key.getStr("TABLEKEY_TYPE"))) {
            builder.append("alter table " + resourceTable.getStr("RESOURCETABLE_TABLECODE") + " \n " +
                    "add constraint " + key.getStr("TABLEKEY_CODE") + " unique (" + key.getStr("TABLEKEY_COLUMNCODE") + ") "
            );
            lists.add(builder.toString());
        } else if ("Foreign".equals(key.getStr("TABLEKEY_TYPE")) || "Inline".equals(key.getStr("TABLEKEY_TYPE"))) {
            /**
             * 级联关系解释
             * 1.CASCADE（级联）：父表delete、update的时候，子表会delete、update掉关联记录
             * 2.SET NULL（设置空）：父表delete、update的时候，子表会将关联记录的外键字段所在列设为null，所以注意在设计子表时外键不能设为not null
             * 3.NO ACTION（不做处理）：如果想要删除父表的记录时，而在子表中有关联该父表的记录，则不允许删除父表中的记录
             */
            StringBuilder ddl = new StringBuilder("alter table " + resourceTable.getStr("RESOURCETABLE_TABLECODE") + " \n ");
            ddl.append("add constraint " + key.getStr("TABLEKEY_CODE") + " foreign key (" + key.getStr("TABLEKEY_COLUMNCODE") + ") \n ");
            if ("SY_PARENT".equals(key.getStr("TABLEKEY_COLUMNCODE")) && resourceTable.getStr("RESOURCETABLE_TABLECODE").equals(key.getStr("TABLEKEY_LINKTABLE"))) {
                ddl.append("references " + key.getStr("TABLEKEY_LINKTABLE") + " (" + key.getStr("TABLEKEY_LINECOLUMNCODE") + ") ON DELETE CASCADE ON UPDATE NO ACTION");
            } else if ("Cascade".equals(key.getStr("TABLEKEY_LINETYLE"))) {
                ddl.append("references " + key.getStr("TABLEKEY_LINKTABLE") + " (" + key.getStr("TABLEKEY_LINECOLUMNCODE") + ") ON DELETE CASCADE ON UPDATE CASCADE");
            } else if ("Noaction".equals(key.getStr("TABLEKEY_LINETYLE"))) {
                ddl.append("references " + key.getStr("TABLEKEY_LINKTABLE") + " (" + key.getStr("TABLEKEY_LINECOLUMNCODE") + ") ON DELETE NO ACTION ON UPDATE NO ACTION");
            } else if ("Setnull".equals(key.getStr("TABLEKEY_LINETYLE"))) {
                ddl.append("references " + key.getStr("TABLEKEY_LINKTABLE") + " (" + key.getStr("TABLEKEY_LINECOLUMNCODE") + ") ON DELETE SET NULL ON UPDATE SET NULL");
            }
            builder.append(ddl.toString());
            lists.add(builder.toString());
            //处理是否禁用
        }
    }

    /**
     * 辅助函数 生成创建索引的DDL语句
     *
     * @param index
     * @param resourceTable
     * @return
     */
    public String getDDL4AddIndex(DynaBean index, DynaBean resourceTable) {
        StringBuilder builder = new StringBuilder();
        if (index.getStr("TABLEINDEX_FIELDCODE").equals(resourceTable.getStr("RESOURCETABLE_PKCODE"))) {
            return "";
        }
        builder.append("ALTER TABLE " + resourceTable.getStr("RESOURCETABLE_TABLECODE") + " ADD ");
        if ("1".equals(index.getStr("TABLEINDEX_UNIQUE"))) {
            builder.append("UNIQUE");
        }
        builder.append(" INDEX " + index.getStr("TABLEINDEX_NAME") + " ");
        builder.append("(" + index.getStr("TABLEINDEX_FIELDCODE") + ")");
        return builder.toString();
    }

    /**
     * 辅助函数 生成创建索引的DDL语句
     *
     * @return
     */
    public String getDefaultDDL4AddIndex(String tableCode, String columnCode, String indexName) {
        StringBuilder builder = new StringBuilder();
        builder.append("ALTER TABLE " + tableCode + " ADD ");
        builder.append(" INDEX " + indexName + " ");
        builder.append("(" + columnCode + ")");
        return builder.toString();
    }

    /**
     * 辅助函数 生成创建键的DDL语句
     *
     * @param uniqueCode
     * @param columnCode
     * @return
     */
    @Override
    public String getDDL4AddUnqiue(String uniqueCode, String columnCode, String tableCode) {
        StringBuffer ddlSql = new StringBuffer();
        ddlSql.append("ALTER TABLE " + tableCode + " ADD UNIQUE INDEX " + uniqueCode + " (" + columnCode + ")");
        return ddlSql.toString();
    }

    /**
     * 得到创建表的DDL语言
     *
     * @param resourceTable
     * @return
     */
    public List<String> getDDL4CreateTable(DynaBean resourceTable) {
        Boolean jeCore = ("1".equals(resourceTable.getStr("SY_JECORE")));
        List<String> list = new ArrayList<String>();
        StringBuilder createSql = new StringBuilder("CREATE TABLE " + resourceTable.get("RESOURCETABLE_TABLECODE") + " ( \n ");
        List<String> uniqueColumns = new ArrayList<String>();
        List<DynaBean> columns = resourceTable.getDynaBeanList(BeanService.KEY_TABLE_COLUMNS);
        String pkCode = "";
        for (DynaBean column : columns) {
            //提取到外面
            createSql.append(getDDL4AddColumnsForAuto(column, jeCore,resourceTable).replaceAll("@", ","));
            //维护唯一性索引
            if (ColumnType.ID.equals(column.getStr("TABLECOLUMN_TYPE"))) {
                pkCode = column.getStr("TABLECOLUMN_CODE");
                continue;
            }
            if ("1".equals(column.getStr("TABLECOLUMN_UNIQUE")) && !ColumnType.ID.equals(column.getStr("TABLECOLUMN_TYPE")) && !column.getStr("TABLECOLUMN_CODE", "").equals(resourceTable.getStr("RESOURCETABLE_PKCODE"))) {
                uniqueColumns.add(column.getStr("TABLECOLUMN_CODE"));
            }
        }
        //1.生成创建表的基本DDL语句
        String sql = createSql.substring(0, createSql.length() - 2) + " \n )";
        list.add(sql);
        //2.生成修改表的注释
        list.add("alter table " + resourceTable.get("RESOURCETABLE_TABLECODE") + " comment '" + resourceTable.get("RESOURCETABLE_TABLENAME") + "'");
        for (DynaBean column : columns) {
            if ("0".equals(column.get("TABLECOLUMN_ISNULL"))) {
                list.add("alter table " + resourceTable.get("RESOURCETABLE_TABLECODE") + " modify column " + column.get("TABLECOLUMN_CODE") + " " + getDDLFieldType(column, jeCore) + " comment '" + column.get("TABLECOLUMN_NAME") + "' not null ");
            } else {
                list.add("alter table " + resourceTable.get("RESOURCETABLE_TABLECODE") + " modify column " + column.get("TABLECOLUMN_CODE") + " " + getDDLFieldType(column, jeCore) + " comment '" + column.get("TABLECOLUMN_NAME") + "'");
            }
        }
        List<DynaBean> indexs = (List<DynaBean>) resourceTable.get(BeanService.KEY_TABLE_INDEXS);

        //2.生成表中存在的键
        List<DynaBean> keys = (List<DynaBean>) resourceTable.get(BeanService.KEY_TABLE_KEYS);
        for (DynaBean key : keys) {
            String keyCode = key.getStr("TABLEKEY_COLUMNCODE") == null ? "" : key.getStr("TABLEKEY_COLUMNCODE").toUpperCase();
            if ((key.getStr("TABLEKEY_TYPE").equals("Foreign") || key.getStr("TABLEKEY_TYPE").equals("Inline")
            ) && indexsNoContains(indexs, keyCode)) {
                addIndexBeforeAddingForeignKeyCreateTable(key, list);
            }
            getDDL4AddKey(key, resourceTable, list);
        }
        //3.维护唯一性约束
        for (String columnCode : uniqueColumns) {
            String uniqueCode = "JE_UNIQUE_" + DateUtils.getUniqueTime();
            list.add(getDDL4AddUnqiue(uniqueCode, columnCode, resourceTable.getStr("RESOURCETABLE_TABLECODE")));
            list.add("UPDATE JE_CORE_TABLECOLUMN SET TABLECOLUMN_UNIQUECODE='" + uniqueCode + "' WHERE TABLECOLUMN_CODE='" + columnCode + "' AND TABLECOLUMN_RESOURCETABLE_ID='" + resourceTable.getStr("JE_CORE_RESOURCETABLE_ID") + "'");
        }
        //4.加入索引
        for (DynaBean index : indexs) {
            if (pkCode.equals(index.getStr("TABLEINDEX_FIELDCODE"))) {
                continue;
            }
            list.add(getDDL4AddIndex(index, resourceTable));
        }
        return list;
    }

    private void addIndexBeforeAddingForeignKeyCreateTable(DynaBean key, List<String> arraySql) {
        String tableCode = key.getStr("TABLEKEY_TABLECODE");
        String columnCode = key.getStr("TABLEKEY_COLUMNCODE");
        String lineColumncode = key.getStr("TABLEKEY_LINECOLUMNCODE");
        String linkTable = key.getStr("TABLEKEY_LINKTABLE");
        String indexName = key.getStr("TABLEKEY_CODE");
        //查询元数据有没有索引信息,没有就创建
        DynaBean tableIndex = tableIndexRpcService.getTableIndexInfo(columnCode, tableCode);
        if (tableIndex == null) {
            tableIndexRpcService.insertTableIndex(columnCode, tableCode, indexName);
        }


    }

    /**
     * 得到更新表的DDL语言
     *
     * @param resourceTable
     * @return
     */
    public List<String> getDDL4UpdateTable(DynaBean resourceTable, List<DbModel> dbFields) {
        List<String> arraySql = new ArrayList<String>();
        List<String> fieldCodes = new ArrayList<String>();
        for (DbModel dbModel : dbFields) {
            fieldCodes.add(dbModel.getCode());
        }
        Boolean jeCore = ("1".equals(resourceTable.getStr("SY_JECORE")));
        String oldTableCode = resourceTable.getStr("RESOURCETABLE_OLDTABLECODE");
        if (!Strings.isNullOrEmpty(oldTableCode) && !oldTableCode.equals(resourceTable.getStr("RESOURCETABLE_TABLECODE"))) {
            arraySql.add("alter table " + resourceTable.getStr("RESOURCETABLE_OLDTABLECODE") + " rename " + resourceTable.getStr("RESOURCETABLE_TABLECODE") + ";");
        }
        //1.修改表的注释
        arraySql.add("alter table " + resourceTable.get("RESOURCETABLE_TABLECODE") + " comment '" + resourceTable.get("RESOURCETABLE_TABLENAME") + "'");
        //2.修改列信息
        List<DynaBean> columns = resourceTable.getDynaBeanList(BeanService.KEY_TABLE_COLUMNS);
        //需要删除的唯一性约束的字段
        List<String> delUniqueColumns = new ArrayList<String>();
        //需要增加的唯一性约束的字段
        List<String> addUniqueColumns = new ArrayList<String>();

        String isNull = "";
        for (DynaBean column : columns) {
            boolean update = false;
            if ("1".equals(column.getStr("TABLECOLUMN_ISCREATE")) && (fieldCodes.contains(column.getStr("TABLECOLUMN_CODE", "").toUpperCase()) || (fieldCodes.contains(column.getStr("TABLECOLUMN_OLDCODE", "").toUpperCase()))
                    && !column.getStr("TABLECOLUMN_CODE", "").toUpperCase().equals(column.getStr("TABLECOLUMN_OLDCODE", "").toUpperCase()))) {
                update = true;
            } else if (fieldCodes.contains(column.getStr("TABLECOLUMN_CODE", "").toUpperCase())) {
                update = true;
                column.setStr("TABLECOLUMN_OLDCODE", column.getStr("TABLECOLUMN_CODE"));
            } else {
                update = false;
            }
            if (update) {
                //更新 就更新长度和 注释
                //判断是否更改了列名   (不区分大小写)
                if (!column.getStr("TABLECOLUMN_CODE").equalsIgnoreCase(column.getStr("TABLECOLUMN_OLDCODE"))) {
                    //暂时未测试。。  原代码是alter table liuyan change names username varchar(50);  是需要加上字段类型的
                    arraySql.add("alter table " + resourceTable.get("RESOURCETABLE_TABLECODE") + " change " + column.getStr("TABLECOLUMN_OLDCODE") + " " + column.getStr("TABLECOLUMN_CODE") + " " + getDDLFieldType(column, jeCore) + ";");
                }
                if (!column.getStr("TABLECOLUMN_CODE").equals(resourceTable.get("RESOURCETABLE_PKCODE"))) {
                    String isDdl = "alter table " + resourceTable.get("RESOURCETABLE_TABLECODE") + " modify " + getDDL4AddColumns(column, jeCore).replaceAll(",", "").replace("@", ",");
                    arraySql.add(isDdl);
                    if (isDdl.contains("not null")) {
                        isNull = " not null";
                    }

                }
                //维护唯一性索引
                if (resourceTable.get("RESOURCETABLE_PKCODE", "").equals(column.getStr("TABLECOLUMN_CODE"))) {
                    continue;
                }
                String unique = column.getStr("TABLECOLUMN_UNIQUE");
                String oldUnique = column.getStr("TABLECOLUMN_OLDUNIQUE");
                if (!ColumnType.ID.equals(column.getStr("TABLECOLUMN_TYPE"))) {
                    //添加唯一性约束
                    if ("1".equals(unique) && "0".equals(oldUnique)) {
                        addUniqueColumns.add(column.getStr("TABLECOLUMN_CODE"));
                    } else if ("0".equals(unique) && "1".equals(oldUnique)) {
                        delUniqueColumns.add(column.getStr("TABLECOLUMN_UNIQUECODE"));
                    }
                }
            } else {
                //添加
                arraySql.add("alter table " + resourceTable.get("RESOURCETABLE_TABLECODE") + " add " + getDDL4AddColumns(column, jeCore).replaceAll(",", "").replace("@", ","));
                if ("1".equals(column.getStr("TABLECOLUMN_UNIQUE")) && !ColumnType.ID.equals(column.getStr("TABLECOLUMN_TYPE"))) {
                    addUniqueColumns.add(column.getStr("TABLECOLUMN_CODE"));
                }
            }
            arraySql.add("alter table " + resourceTable.get("RESOURCETABLE_TABLECODE") + " modify column " + column.get("TABLECOLUMN_CODE") + " " + getDDLFieldType(column, jeCore) + " comment '" + column.get("TABLECOLUMN_NAME") + "'" + isNull);
            isNull = "";
        }
        //3.维护唯一性约束
        for (String columnCode : addUniqueColumns) {
            String uniqueCode = "JE_UNIQUE_" + DateUtils.getUniqueTime();
            arraySql.add(getDDL4AddUnqiue(uniqueCode, columnCode, resourceTable.getStr("RESOURCETABLE_TABLECODE")));
            arraySql.add("UPDATE JE_CORE_TABLECOLUMN SET TABLECOLUMN_UNIQUECODE='" + uniqueCode + "' WHERE TABLECOLUMN_CODE='" + columnCode + "' AND TABLECOLUMN_RESOURCETABLE_ID='" + resourceTable.getStr("JE_CORE_RESOURCETABLE_ID") + "'");
        }
        for (String uniqueCode : delUniqueColumns) {
            arraySql.add("ALTER TABLE " + resourceTable.getStr("RESOURCETABLE_TABLECODE") + " DROP INDEX " + uniqueCode + " ");
        }
        //3.加入索引
        List<DynaBean> indexs = (List<DynaBean>) resourceTable.get(BeanService.KEY_TABLE_INDEXS);
        for (DynaBean index : indexs) {
            if ("1".equals(index.getStr("TABLEINDEX_ISCREATE"))) {
                //不支持更新索引

            } else {
                arraySql.add(getDDL4AddIndex(index, resourceTable));
            }
        }
        //4.修改键信息，创建外键时，查询表是否存在索引，如果不存在则添加索引信息
        List<DynaBean> keys = (List<DynaBean>) resourceTable.get(BeanService.KEY_TABLE_KEYS);
        for (DynaBean key : keys) {
            if ("1".equals(key.getStr("TABLEKEY_ISCREATE"))) {
                //更新
                //暂时不能更新,只能添加
            } else {
                //添加,如果是唯一约束，则不自动添加索引
                String keyCode = key.getStr("TABLEKEY_COLUMNCODE") == null ? "" : key.getStr("TABLEKEY_COLUMNCODE").toUpperCase();
                if (!key.getStr("TABLEKEY_TYPE").equals("Unique") && indexsNoContains(indexs, keyCode)) {
                    addIndexBeforeAddingForeignKey(key, arraySql);
                }
                getDDL4AddKey(key, resourceTable, arraySql);
            }
        }
        return arraySql;
    }

    private boolean indexsNoContains(List<DynaBean> indexs, String keyCode) {
        if (StringUtil.isEmpty(keyCode)) {
            return true;
        }
        for (DynaBean dynaBean : indexs) {
            String codeIndex = dynaBean.getStr("TABLEINDEX_FIELDCODE") == null ? "" : dynaBean.getStr("TABLEINDEX_FIELDCODE").toUpperCase();
            if (codeIndex.equals(keyCode)) {
                return false;
            }
        }
        return true;
    }

    /**
     * 添加外键前添加索引
     *
     * @param key
     * @param indexs
     * @param arraySql
     */
    public void addIndexBeforeAddingForeignKey(DynaBean key, List<String> arraySql) {
        String tableCode = key.getStr("TABLEKEY_TABLECODE");
        String columnCode = key.getStr("TABLEKEY_COLUMNCODE");
        String lineColumncode = key.getStr("TABLEKEY_LINECOLUMNCODE");
        String linkTable = key.getStr("TABLEKEY_LINKTABLE");
        //查询元数据有没有索引信息,没有就创建
        //查询数据库有没有索引信息
        List<Map<String, Object>> tableIndexList = tableIndexRpcService.getIndexInfosToDatabase(tableCode, columnCode);
        DynaBean tableIndex = tableIndexRpcService.getTableIndexInfo(columnCode, tableCode);
        String indexName = "";
        if (tableIndex == null) {
            if (tableIndexList.size() > 0) {
                indexName = (String) tableIndexList.get(0).get("Key_name");
            }
            tableIndex = tableIndexRpcService.insertTableIndex(columnCode, tableCode, indexName);
        }
        if (tableIndexList.size() == 0) {
            arraySql.add(getDefaultDDL4AddIndex(tableCode, columnCode, tableIndex.getStr("TABLEINDEX_NAME")));
        }
        List<Map<String, Object>> linkTableIndexList = tableIndexRpcService.getIndexInfosToDatabase(linkTable, lineColumncode);
        if (linkTableIndexList == null) {
            return;
        }
        DynaBean linkTableIndex = tableIndexRpcService.getTableIndexInfo(lineColumncode, linkTable);
        String linkIndexName = "";
        if (linkTableIndex == null) {
            if (linkTableIndexList.size() > 0) {
                linkIndexName = (String) linkTableIndexList.get(0).get("Key_name");
            }
            linkTableIndex = tableIndexRpcService.insertTableIndex(lineColumncode, linkTable, linkIndexName);
        }
        //查询数据库有没有索引信息
        if (linkTableIndexList.size() == 0) {
            arraySql.add(getDefaultDDL4AddIndex(linkTable, lineColumncode, linkTableIndex.getStr("TABLEINDEX_NAME")));
        }
    }

    /**
     * 得到用于excel导出功能的sql ,主要工作是吧ddcode格式化成ddName
     *
     * @param tableName
     * @param whereSql
     * @param fieldsCode
     * @param whereSql
     * @param fieldDD
     * @return
     */
    public String getSql4Exp(String tableName, String fieldsCode, String fieldDD, String whereSql, String orderSql) {
        JSONObject jb = JSONObject.fromObject(fieldDD);
        StringBuffer sql = new StringBuffer(
                "select " +
                        assistGetSql4Exp(fieldsCode.split(ArrayUtils.SPLIT), jb) +
                        " from " + tableName + " PCAT2" +
                        " where 1=1 " + whereSql + " " + orderSql
        );

        return sql.toString();
    }

    /**
     * 辅助函数
     *
     * @return
     */
    @Override
    public String assistGetSql4Exp(String[] fieldsCode, JSONObject jb) {
        StringBuffer str = new StringBuffer();
        for (int i = 0; i < fieldsCode.length; i++) {
            if (jb.get(fieldsCode[i]) == null) {//不用字典
                str.append(fieldsCode[i] + ArrayUtils.SPLIT);
            } else {//用到字典(只支持内部字典)
                str.append("(select DICTIONARYITEM_ITEMNAME from JE_CORE_DICTIONARY p, JE_CORE_DICTIONARYITEM c where " +
                        "p.DICTIONARY_DDCODE = '" + jb.get(fieldsCode[i]) +
                        "' and p.JE_CORE_DICTIONARY_ID = c.DICTIONARYITEM_DICTIONARY_ID and c.DICTIONARY_ITEMCODE = PCAT2." + fieldsCode[i] + ") " + fieldsCode[i] + ",");
            }
        }
        String over = str.substring(0, str.length() - 1);
        return over;
    }

    /**
     * 研发部:云凤程
     * 生成数据库的查询操作(全部是通过预处理的)
     *
     * @param dynaBean
     * @return
     */
    @Override
    public String getInsertSql(DynaBean dynaBean) {
        //1.得到表结构的描述信息
        String tableCode = dynaBean.getStr(BeanService.KEY_TABLE_CODE);
        DynaBean table = beanService.getResourceTable(tableCode);
        //2.拼装预处理的SQL
        StringBuilder insertSql = new StringBuilder();
        String fieldNames = beanService.getNames4Sql(table);//表字典集合用","分开
        String values = beanService.getValues4Sql(table);//表字典集合用 格式化成 :name,:age
        insertSql.append("INSERT INTO " + tableCode + "(" + fieldNames + ") VALUES (" + values + ")");
        return insertSql.toString();
    }

    /**
     * 研发部:云凤程
     * 生成数据库的根据ID删除的SQl
     *
     * @param dynaBean
     * @return
     */
    public String getDeleteByIdSql(DynaBean dynaBean) {
        //1.得到表结构的描述信息
        String tableCode = dynaBean.getStr(BeanService.KEY_TABLE_CODE);
        //2.得到表的主键编码
        String tablePKCode = dynaBean.getStr(BeanService.KEY_PK_CODE);
        //3.得到表主键的值
        String tablePKValue = dynaBean.getStr(tablePKCode);
        if (tablePKValue == null || "".equals(tablePKValue)) {
            throw new PlatformException("拼装删除语句保存id不能使空或者是空...", PlatformExceptionEnum.JE_CORE_DYNABEAN_ERROR);
        }
        //4.拼SQL
        String deleteSql = "DELETE FROM " + tableCode + " WHERE " + tablePKCode + " = ?";
        return deleteSql;
    }

    /**
     * 研发部:云凤程
     * 生成数据根据where删除的SQL
     *
     * @param dynaBean
     * @return
     */
    public String getDeleteByWhereSql(DynaBean dynaBean) {
        //1.得到表结构的描述信息
        String tableCode = dynaBean.getStr(BeanService.KEY_TABLE_CODE);
        //2.得到删除的条件
        String whereSql = dynaBean.getStr(BeanService.KEY_WHERE);
        //3.拼接SQL 注意:where 条件一定要用 and 开始
        String deleteSql = "DELETE FROM " + tableCode + " WHERE 1=1 " + whereSql;
        return deleteSql;
    }

    /**
     * 研发部:云凤程
     * 生成数据根据where删除的SQL
     *
     * @param ids       用,分开的主键id串
     * @param tableName 表名称
     * @param idName    表主键名字
     * @return
     */
    public String getDeleteByIdsSql(String ids, String tableName, String idName) {
        //1.格式化好sql中用于in操作的id字符串
        Integer paramCount = 0;
        for (String id : ids.split(",")) {
            if (!Strings.isNullOrEmpty(id)) {
                paramCount++;
            }
        }
        String[] idArray = new String[paramCount];
        for (int i = 0; i < paramCount; i++) {
            idArray[i] = "?";
        }
        String idValues = Joiner.on(",").join(idArray);
        if (paramCount <= 0) {
            idValues = "''";
        }
        String deleteSql = "DELETE FROM " + tableName + " WHERE " + idName + " in (" + idValues + ")";
        return deleteSql;
    }

    /**
     * 研发部:云凤程
     * 生成根据id查询一条数据的SQL
     *
     * @param dynaBean
     * @return
     */
    public String getSelecOnetByIdSql(DynaBean dynaBean) {
        //1.得到表结构的描述信息
        String tableCode = dynaBean.getStr(BeanService.KEY_TABLE_CODE);
        //2.得到表的主键编码
        String tablePKCode = dynaBean.getStr(BeanService.KEY_PK_CODE);
//		//3.得到表主键的值
//		String tablePKValue = dynaBean.getStr(tablePKCode);
        //4.得到要查询的列
        String tableFileds = dynaBean.getStr(BeanService.KEY_QUERY_FIELDS, "");
        if (Strings.isNullOrEmpty(tableFileds)) {
            tableFileds = dynaBean.getStr(BeanService.DEF_ALL_FIELDS);
        }
        //5.拼接SQl
        String seletcSql = "SELECT " + tableFileds + " FROM " + tableCode + " WHERE " + tablePKCode + " = ?";
        return seletcSql;
    }

    /**
     * 研发部:云凤程
     * 生成根据WHERE查询一条数据的SQL
     *
     * @param dynaBean
     * @return
     */
    public String getSelectOneByWhereSql(DynaBean dynaBean) {
        //1.得到表结构的描述信息
        String tableCode = dynaBean.getStr(BeanService.KEY_TABLE_CODE);
        //2.得到查询条件
        String where = dynaBean.getStr(BeanService.KEY_WHERE);
        //3.得到要查询的列
        String tableFileds = dynaBean.getStr(BeanService.KEY_QUERY_FIELDS, "");
        if (Strings.isNullOrEmpty(tableFileds)) {
            tableFileds = dynaBean.getStr(BeanService.DEF_ALL_FIELDS);
        }
        //4.拼接SQl
        String seletcSql = "SELECT " + tableFileds + " FROM " + tableCode + " WHERE 1=1 " + where;
        if (dynaBean.get(BeanService.KEY_ORDER) != null && !"".equals(dynaBean.get(BeanService.KEY_ORDER))) {
            seletcSql = seletcSql + " " + dynaBean.get(BeanService.KEY_ORDER);
        }
        return seletcSql;
    }

    /**
     * 研发部:云凤程
     * 生成根据WHERE查询数据的记录数
     *
     * @param dynaBean
     * @return
     */
    public String getSelectCountByWhereSql(DynaBean dynaBean) {
        //1.得到表结构的描述信息
        String tableCode = dynaBean.getStr(BeanService.KEY_TABLE_CODE);
        //2.得到查询条件
        String where = dynaBean.getStr(BeanService.KEY_WHERE);
        //3.拼接SQl
        String seletcSql = "SELECT COUNT(*) FROM " + tableCode + " WHERE 1=1 " + where;
        return seletcSql;
    }

    /**
     * 研发部:云凤程
     * 生成根据ID或者where生成更新BEAN的SQL
     *
     * @param useWhere 是否用whereh
     * @param dynaBean
     * @return
     */
    public String getUpdateByIdOrWhereSql(DynaBean dynaBean, boolean useWhere) {
        //1.得到表结构
        String tabelCode = dynaBean.getStr(BeanService.KEY_TABLE_CODE);
        DynaBean table = beanService.getResourceTable(tabelCode);
        //2.得到列模式
//		List<DynaBean> columns = (List<DynaBean>) table.get(BeanUtils.KEY_TABLE_COLUMNS);
        //3.得到表的主键编码
        String tablePKCode = dynaBean.getStr(BeanService.KEY_PK_CODE);
        //4.得到表主键的值
        String tablePKValue = dynaBean.getStr(tablePKCode);
        String where = dynaBean.getStr(BeanService.KEY_WHERE);
        //5.拼接SQL
        StringBuilder updateSql = new StringBuilder("UPDATE " + tabelCode + " SET ");
        Map<String, Object> map = dynaBean.getValues();
        //获取执行修改值的修改语句
        String updateInfos = beanService.getUpdateInfos4Sql(table, map);//表字典集合用","分开
        updateSql.append(updateInfos);
        if (useWhere) {
            if (!Strings.isNullOrEmpty(where)) {
                updateSql.append(" WHERE 1=1 " + where);
            }
        } else {
            updateSql.append(" WHERE " + tablePKCode + " = :" + tablePKCode + "");
        }
        return updateSql.toString();
    }

    public List<String> getDeleteColumnSql(String tableCode, List<DynaBean> columns) {
        List<String> delSqls = new ArrayList<String>();
        for (DynaBean tc : columns) {
            if ("1".equals(tc.getStr("TABLECOLUMN_UNIQUE"))
                    && "1".equals(tc.getStr("TABLECOLUMN_OLDUNIQUE"))
                    && StringUtil.isNotEmpty(tc.getStr("TABLECOLUMN_UNIQUECODE"))) {
                delSqls.add("ALTER TABLE '" + tableCode + "' DROP INDEX " + tc.getStr("TABLECOLUMN_UNIQUECODE") + " \n ");
            }
            if (!Strings.isNullOrEmpty(tc.getStr("TABLECOLUMN_CODE")) && "1".equals(tc.getStr("TABLECOLUMN_ISCREATE"))) {
                delSqls.add("alter table " + tableCode + " drop COLUMN " + tc.getStr("TABLECOLUMN_CODE") + " \n ");
            }
        }
        return delSqls;
    }

    /**
     * 删除键的ddl语句
     *
     * @param tableCode
     * @param keys
     * @return
     */
    public String getDeleteKeySql(String tableCode, List<DynaBean> keys) {
        StringBuffer delSql = new StringBuffer();
        for (DynaBean key : keys) {
            if (!Strings.isNullOrEmpty(key.getStr("TABLEKEY_CODE")) && "1".equals(key.getStr("TABLEKEY_ISCREATE"))) {
                if ("Foreign".equals(key.getStr("TABLEKEY_TYPE")) || "Inline".equals(key.getStr("TABLEKEY_TYPE"))) {
                    delSql.append(" alter table " + tableCode + " DROP FOREIGN KEY " + key.getStr("TABLEKEY_CODE") + " \n ;");
                } else if ("Unique".equals(key.getStr("TABLEKEY_TYPE"))) {
                    delSql.append(" alter table " + tableCode + " DROP INDEX " + key.getStr("TABLEKEY_CODE") + " \n ;");
                }
            }
        }
        return delSql.toString();
    }

    /**
     * 删除索引的ddl语句
     *
     * @param tableCode
     * @param indexs
     * @return
     */
    public String getDeleteIndexSql(String tableCode, List<DynaBean> indexs) {
        StringBuffer delSql = new StringBuffer();
        for (DynaBean index : indexs) {
            if (!Strings.isNullOrEmpty(index.getStr("TABLEINDEX_NAME")) && "1".equals(index.getStr("TABLEINDEX_ISCREATE"))) {
                delSql.append("ALTER TABLE " + tableCode + " DROP INDEX " + index.getStr("TABLEINDEX_NAME") + " \n ;");
            }
        }
        return delSql.toString();
    }

    /**
     * 得到Bean修改的sql语句 (主要用于表格列表更新)
     *
     * @param tableCode
     * @param pkName
     * @param changes
     * @return
     */
    public String getUpdateSql(String tableCode, String pkName, Map changes) {
        StringBuffer updateSql = new StringBuffer();
        String getQueryFields = beanService.getQueryFields(tableCode, new String[0]);
        logger.info("获取的字段=" + getQueryFields);
        String[] split = getQueryFields.split(",");
        List<String> filedList = Arrays.asList(split);

        updateSql.append("UPDATE " + tableCode + " SET ");
        String pkValue = "";
        for (Object obj : changes.entrySet()) {
            Map.Entry entry = (Map.Entry) obj;
            String k = StringUtil.getDefaultValue(entry.getKey(), "");
            if (!filedList.contains(k) || "__PK_VALUE".equals(k) || "_X_ROW_KEY".equals(k)) {
                logger.info("被过滤的key=" + k);
                continue;
            }

            if (pkName.equals(k)) {
                pkValue = StringUtil.getDefaultValue(entry.getValue(), "");
                continue;
            }
            String v = null;
            if (entry.getValue() != null) {
                v = StringUtil.getDefaultValue(entry.getValue(), "");
            }
            if (v == null) {
                updateSql.append(k + "=NULL,");
            } else {
                updateSql.append(k + "='" + v + "',");
            }
        }
        updateSql.deleteCharAt(updateSql.length() - 1);
        updateSql.append(" WHERE " + pkName + "='" + pkValue + "'");
        logger.info("最终的sql=" + updateSql.toString());
        return updateSql.toString();
    }

}
